using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoreData;
using EntityData;
using Helper;
namespace Services
{

    public partial class BeginStock_DetailService
    {
       readonly IBeginStock_DetailEntity entity;

       public BeginStock_DetailService()
       {
           this.entity = new BeginStock_DetailEntity();

       }
       private string StrGetBeginStock = @"select TI.ID as ProductID,TI.UnitID,TI.UnitName,TI.ProductName as ProductName,TI.Price,TI.ProductGroup,TI.Code as ProductCode
,TI.BeginStockQuantity + isnull((TI.InStockQuantity*Ratio)-isnull(sum(vod.Quantity),0),0) as OldQuantity,Ratio
,NULL AS NewQuantity
from ( 
select p.ID,p.Output_UnitID as UnitID,P.Output_Price as Price,p.Code
,pus.Name as UnitName,p.Name as ProductName,pg.Name as ProductGroup
,vi.WarehouseID
,isnull(sum(bd.NewQuantity),0) as BeginStockQuantity
,isnull(sum(vid.Quantity),0)as InStockQuantity
,CASE WHEN p.Output_UnitID <> vid.UnitID THEN
	(SELECT puc.Ratio from Product_UnitChange puc where puc.UnitChangeID=p.Output_UnitID and puc.ProductID=p.ID)
	ELSE 1
END as Ratio
from Product p
left join Stock_VoucherInDetail vid on P.ID=vid.ProductID
left join Product_Unit pus on pus.ID=p.Output_UnitID
left join Product_Group pg on pg.ID=p.Product_GroupID
left join BeginStock_Detail bd on p.ID=bd.ProductID
left join Stock_VoucherIn vi on vi.ID=vid.Stock_VoucherInID
group by p.ID,vid.UnitID,p.Name,p.Output_UnitID,pus.Name,P.Output_Price,pg.Name,p.Code,vi.WarehouseID
) TI
left join Stock_VoucherOutDetail vod on vod.ProductID=TI.ID
left join Stock_VoucherOut vo on vo.ID=vod.Stock_VoucherOutID
group by TI.ID,TI.UnitID,TI.UnitName,TI.ProductName,Ratio,TI.InStockQuantity,TI.Price,TI.ProductGroup,TI.Code,TI.BeginStockQuantity";
       private string StrGetBeginStockByPID = @"Select P.ID as ProductID, P.Code as ProductCode,P.Name as ProductName, PU.Name as UnitName,PU.ID as UnitID,0 as NewQuantity From Product P
left join Product_Unit PU on PU.ID=P.Input_UnitID {0}";
       #region Main Method

       /// <summary>
       /// Save 
       /// </summary>
       /// <param name="_model"></param>
       /// <returns></returns>
       public long Save(object _model)
       {
           try
           {
               var id = long.Parse(_model.GetType().GetProperty("ID").GetValue(_model, null).ToString());
               if (id == 0)
               {
                   return entity.Save((CoreData.BeginStock_Detail)_model, Table.BeginStock_Detail.ToString());
               }
               else
               {
                   return entity.Update((CoreData.BeginStock_Detail)_model, Table.BeginStock_Detail.ToString());
               }
           }
           catch { return -1; }
       }

       /// <summary>
       /// Get by ID
       /// </summary>
       /// <param name="_id"></param>
       /// <returns></returns>
       public CoreData.BeginStock_Detail GetByID(long _id)
       {
           try
           {
               return entity.GetById(_id, Table.BeginStock_Detail.ToString());
           }
           catch { return null; }
       }

       /// <summary>
       /// Get List
       /// </summary>
       /// <returns></returns>
       public IList<CoreData.BeginStock_Detail> GetList()
       {
           try
           {
               return entity.GetAll(Table.BeginStock_Detail.ToString()).ToList();
           }
           catch { return null; }

       }

       /// <summary>
       /// Get List with Linq
       /// </summary>
       /// <returns></returns>
       public IList<CoreData.BeginStock_Detail> GetListByLINQ(Func<CoreData.BeginStock_Detail, Boolean> _where)
       {
           try
           {
               return entity.GetMany(_where, Table.BeginStock_Detail.ToString()).ToList();
           }
           catch { return null; }
       }
       /// <summary>
       /// Get One with Linq
       /// </summary>
       /// <returns></returns>
       public CoreData.BeginStock_Detail GetOneByLINQ(Func<CoreData.BeginStock_Detail, Boolean> _where)
       {
           try
           {
               return entity.Get(_where, Table.BeginStock_Detail.ToString());
           }
           catch { return null; }
       }

       /// <summary>
       /// Get List with search string
       /// </summary>
       /// <returns></returns>
       public IList<CoreData.BeginStock_Detail> GetList(string _searchstring)
       {
           //search theo tieu chi nao do         
           try
           {
               return entity.GetBySearchString(_searchstring, Table.BeginStock_Detail.ToString()).ToList();
           }
           catch { return null; }
       }


       /// <summary>
       /// Delete
       /// </summary>
       /// <param name="_model"></param>
       /// <returns></returns>
       public bool Delete(object _model)
       {
           try
           {
               entity.Delete((CoreData.BeginStock_Detail)_model, Table.BeginStock_Detail.ToString());
               return true;
           }
           catch { return false; }
       }


       #endregion



        #region Other Method

        /// <summary>
        /// them moi, kiem ke dau ki
        /// </summary>
        /// <returns></returns>
       public IList<CoreData.BeginStock_Detail> GetList_NewBegin()
       {
           try
           {
               return entity.GetByCusTomSQL(StrGetBeginStock).ToList();
           }
           catch { return null; }

       }
       public IList<CoreData.BeginStock_Detail> GetList_NewBeginByListPID(List<long> ProductSeletedIDs)
       {
           try
           {
               if (ProductSeletedIDs.Count > 0)
               {
                   string _where = string.Format("where P.ID IN ({0})", string.Join(",", ProductSeletedIDs.ToArray()));

                   return entity.GetByCusTomSQL(String.Format(StrGetBeginStockByPID, _where)).ToList();
               }
               else
               {
                   return entity.GetByCusTomSQL(String.Format(StrGetBeginStockByPID, " where 1=1")).ToList();
               }
               
           }
           catch { return null; }

       }
        #endregion 
    
    }
         
}
